<?php


namespace app\admin\controller;

use app\admin\service\UploadFileService;
use app\common\controller\AdminBaseController;
use app\common\model\tang\BalanceLog;
use app\common\model\tang\Cost as CostModel;
use app\common\model\tang\MonthElectricityDegree;
use app\common\model\tang\MonthElectricityRecord;
use app\common\model\tang\MonthWaterLog as WaterModel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\facade\Db;
use app\common\model\tang\Merchant;
use think\Exception;

class MonthWaterLog extends AdminBaseController
{
    protected function initialize()
    {
        parent::initialize(); // TODO: Change the autogenerated stub
        $this->model = new WaterModel();
    }

    //电费账单列表
    public function index()
    {
        if (IS_AJAX) {
            return $this->model->getAdminPageData($this->param, 'id desc', []);
        }
        $merchant_list = getMerchant();
        //获取菜单列表
        $this->assign(compact("merchant_list"));
        return $this->fetch();
    }
    public function exportIn(){
        $this->model->getExportPageData($this->param, 'id desc');
    }
    public function export()
    {
        $title = ['公司名称', '海鼎合同号', '华立户号', '设备编号','月份', '倍率','起始值','终止值','用量'];
        return export_excel('充值记录_' . date('Y-m-d'), $title, []);
    }
    /**
     * 导入
     */
    public function import()
    {
        header("content-type:text/html;charset=utf-8");
        //上传excel文件
        $res = UploadFileService::instance()->setUploadPath('store/excel')->image('file');
        if ($res) {
            //获取上传到后台的文件名
            $fileName = $res['name'];
            //获取文件路径
            $filePath = app()->getRootPath() . 'public' . $res['thumb_path'];
            //获取文件后缀
            $suffix = $res['ext'];
            // 有Xls和Xlsx格式两种
            $reader = IOFactory::createReader('Xlsx');
        } else {
            return json(['status' => '1', 'message' => '文件过大或格式不正确导致上传失败-_-!']);
        }
//        $spreadsheet = $reader->load($filePath);

        //载入excel文件
//        $excel = $reader->load($filePath);
        $excel = IOFactory::load($filePath);
        //读取第一张表
        $sheet = $excel->getSheet(0);
        //获取总行数
        $row_num = $sheet->getHighestRow();
        $merchantModel = new \app\common\model\tang\Merchant();
        $import_data = []; //数组形式获取表格数据
        try {
            for ($i = 2; $i <= $row_num; $i++) {
                $data = [];
                $data['company'] = $excel->getActiveSheet()->getCell("A" . $i)->getValue();
                $data['code'] = $excel->getActiveSheet()->getCell("B" . $i)->getValue();
                $merchant_id = $merchantModel->where('code',$data['code'])->value('id');
                if(empty($merchant_id)){
                    throw new Exception('第'.$i.'行'. $data['code'].'未录入商户信息');
                }
                $data['merchant_id'] =$merchant_id;
                $data['userId'] =$excel->getActiveSheet()->getCell("C" . $i)->getValue();
                $data['pointId'] =trim($excel->getActiveSheet()->getCell("D" . $i)->getValue());
                $data['dataTime'] =trim($excel->getActiveSheet()->getCell("E" . $i)->getValue());
                if(is_numeric( $data['dataTime'])){
                    $data['dataTime'] = date('Y-m', \PHPExcel_Shared_Date::ExcelToPHP($excel->getActiveSheet()->getCell("E" . $i)->getValue()));
                }
                $data['start'] =$excel->getActiveSheet()->getCell("G" . $i)->getValue();
                $data['end'] =$excel->getActiveSheet()->getCell("H" . $i)->getValue();
                $data['rate'] =1;
                $import_data[] = $data;
            }
            (new \app\common\model\tang\MonthWaterLog())->saveAll($import_data);
        } catch (Exception $e) {
            return self::ajaxResult([], 0, 1, $e->getMessage());
        }

        return self::ajaxResult([], 0, 1, '导入成功');
    }

}